package com.xy_pro.xymanager.repository;

import com.xy_pro.xymanager.dto.ScoreTitleDTO;
import com.xy_pro.xymanager.entity.ScoreData;
import jakarta.transaction.Transactional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Optional;

public interface ScoreDataRepository extends JpaRepository<ScoreData, Long> {
    Optional<ScoreData> findScoreDataByNameAndTitle(String name, String title);
    // 查询 title 列的所有不重复值
    // 使用构造函数表达式直接返回DTO
    @Query("SELECT NEW com.xy_pro.xymanager.dto.ScoreTitleDTO(s.title, MIN(s.createTime), s.isVisible) " +
            "FROM ScoreData s " +
            "WHERE (:title IS NULL OR s.title LIKE %:title%) " +
            "GROUP BY s.title, s.isVisible " +
            "ORDER BY MIN(s.createTime) DESC") // 添加排序，DESC表示降序，可根据需要改为ASC
    Page<ScoreTitleDTO> findDistinctByTitle(String title, Pageable pageable);
    @Query("SELECT NEW com.xy_pro.xymanager.dto.ScoreTitleDTO(s.title, MIN(s.createTime), s.isVisible) " +
            "FROM ScoreData s " +
            "GROUP BY s.title, s.isVisible " +
            "ORDER BY MIN(s.createTime) DESC") // 按创建时间降序排序
    Page<ScoreTitleDTO> findDistinctTitle(Pageable pageable);
    @Modifying
    @Transactional
    @Query("UPDATE ScoreData s SET s.isVisible = :isVisible WHERE s.title = :title")
    int updateIsVisibleByTitle(String title, boolean isVisible);

    Optional<ScoreData> findTopByNameOrderByCreateTimeDesc(String name);
}
